package com.vfa.data;


import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DataHelper {

    static final String DATABASE_NAME = "dictionary.db";
    static final int DATABASE_VERSION = 3;
    static final String TABLE_WORDS = "TableWords";
    static final String COLUMN_WordsID = "ID";
    static final String COLUMN_WordsKEY_WORD = "KeyWord";
    static final String COLUMN_WordsCATEGORY = "Category";
    static final String COLUMN_WordsLANGUAGE = "Language";
    static final String COLUMN_WordsMEAN = "MeanOfWord";
    static final String COLUMN_WordsRead = "Read";
    static final String COLUMN_WordsEXAMPLE = "Example";
    static final String COLUMN_WordsDATE = "Date";
    static final String COLUMN_WordsTYPE = "TypeInsert";
    static final String COLUMN_ANSWER_PASS = "AnswerPass";
    static final String COLUMN_ANSWER_FAIL = "AnswerFail";

    static final String TABLE_USERS = "TableUserList";
    static final String COLUMN_UserID = "UserID";
    static final String COLUMN_UserName = "UserName";

    static final String TABLE_HistoryTest = "TableHistoryTest";
    static final String COLUMN_HistoryID = "HistoryID";
    static final String COLUMN_HistoryUserName = "HistoryUserName";
    static final String COLUMN_HistoryTopic = "Topic";
    static final String COLUMN_HistoryKeyQuestion = "KeyQuestion";
    static final String COLUMN_HistoryResult = "Result";
    static final String COLUMN_HistoryAnswer1 = "Answer1";
    static final String COLUMN_HistoryAnswer2 = "Answer2";
    static final String COLUMN_HistoryAnswer3 = "Answer3";
    static final String COLUMN_HistoryAnswer4 = "Answer4";
    static final String COLUMN_HistoryRightAnswer = "RightAnswer";
    static final String COLUMN_HistoryDataTest = "DateTest";

    static final String TABLE_CATEGORY = "TableCategory";
    static final String COLUMN_CategoryID = "CategoryID";
    static final String COLUMN_CategoryName = "CategoryName";

    static final String TABLE_LANGUAGE = "TableLanguage";
    static final String COLUMN_LanguageID = "LanguageID";
    static final String COLUMN_LanguageName = "LanguageName";
    
    // them bang Quiz va Quiz_Words
    static final String TABLE_QUIZ = "TableQuiz";
    static final String COLUMN_QuizID = "QuizID";
    static final String COLUMN_QuizName = "QuizName";
    
    static final String TABLE_QUIZWORDS = "TableQuizWords";
    static final String COLUMN_QuizWord_QuizID = "QuizID";
    static final String COLUMN_QuizWords_WordsID = "WordsID";
    

    
    private Context context;
    protected SQLiteDatabase db;

    public DataHelper(Context context) {
         this.context = context;
         OpenHelper openHelper = new OpenHelper(this.context);
         this.db = openHelper.getWritableDatabase();
    }

    public class OpenHelper extends SQLiteOpenHelper {

        public OpenHelper(Context context) {
            // TODO Auto-generated constructor stub
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        
        
        @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub
            db.execSQL("CREATE TABLE " + TABLE_CATEGORY + " ("
                    + COLUMN_CategoryID 		+ " integer primary key autoincrement, "
                    + COLUMN_CategoryName 	+ " Text not null"
                    +")");

            db.execSQL("CREATE TABLE " + TABLE_LANGUAGE + " ("
                    + COLUMN_LanguageID 		+ " integer primary key autoincrement, "
                    + COLUMN_LanguageName 	+ " Text not null"
                    +")");

            db.execSQL("CREATE TABLE " + TABLE_WORDS + " ("
                    + COLUMN_WordsID 		+ " integer primary key autoincrement, "
                    + COLUMN_WordsKEY_WORD 	+ " Text not null, "
                    + COLUMN_WordsCATEGORY 	+ " integer not null, "
                    + COLUMN_WordsLANGUAGE 	+ " integer not null, "
                    + COLUMN_WordsMEAN 		+ " Text not null, "
                    + COLUMN_WordsRead		+ " Text, "
                    + COLUMN_WordsEXAMPLE 	+ " Text, "
                    + COLUMN_WordsDATE 		+ " Timestamp default current_timestamp, "
                    + COLUMN_WordsTYPE 		+ " Text not null, "
                    + "FOREIGN KEY("+ COLUMN_WordsCATEGORY +") REFERENCES "+ TABLE_CATEGORY +"("+ COLUMN_CategoryID +"),"
                    + "FOREIGN KEY("+ COLUMN_WordsLANGUAGE +") REFERENCES "+ TABLE_LANGUAGE +"("+ COLUMN_LanguageID +")"
                    +")");

            db.execSQL("CREATE TABLE " + TABLE_USERS + " ("
                    + COLUMN_UserID 		+ " integer primary key autoincrement, "
                    + COLUMN_UserName 	+ " Text not null"
                    +")");

            db.execSQL("CREATE TABLE " + TABLE_HistoryTest + " ("
                    + COLUMN_HistoryID 		+ " integer primary key autoincrement, "
                    + COLUMN_HistoryUserName 	+ " Text not null, "
                    + COLUMN_HistoryTopic 	+ " Text not null, "
                    + COLUMN_HistoryKeyQuestion 	+ " Text not null, "
                    + COLUMN_HistoryAnswer1 	+ " Text not null, "
                    + COLUMN_HistoryAnswer2 	+ " Text not null, "
                    + COLUMN_HistoryAnswer3 	+ " Text not null, "
                    + COLUMN_HistoryAnswer4 	+ " Text not null, "
                    + COLUMN_HistoryRightAnswer 	+ " Text not null, "
                    + COLUMN_HistoryResult		+ " Text not null, "
                    + COLUMN_HistoryDataTest 	+ " Timestamp default current_timestamp, "
                    + "FOREIGN KEY (" + COLUMN_HistoryUserName + ") REFERENCES " + TABLE_USERS + "(" + COLUMN_UserName +")"
                    +")");

// them 2 table ngay 24/11
            db.execSQL("CREATE TABLE " + TABLE_QUIZ + " ("
                    + COLUMN_QuizID 		+ " integer primary key autoincrement, "
                    + COLUMN_QuizName 	+ " Text not null"
                    +")");
            System.out.println("CREATE TABLE " + TABLE_QUIZ);
//            String[] unit = {"1", "2", "3", "4"};
//            for(int i = 0; i < unit.length; i++){
//            	db.execSQL("INSERT INTO "+ TABLE_QUIZ + " (" + COLUMN_QuizName + ") VALUES ('" + unit[i] + "')");
//            }
            
            db.execSQL("CREATE TABLE " + TABLE_QUIZWORDS + " ("
                    + COLUMN_QuizWord_QuizID 		+ " integer not null, "
                    + COLUMN_QuizWords_WordsID 	+ " integer not null, PRIMARY KEY("+COLUMN_QuizWord_QuizID+" , " + COLUMN_QuizWords_WordsID+" ),"
                    + "FOREIGN KEY (" + COLUMN_QuizWord_QuizID + ") REFERENCES " + TABLE_QUIZ + "(" + COLUMN_QuizID +"),"
                    + "FOREIGN KEY (" + COLUMN_QuizWords_WordsID + ") REFERENCES " + TABLE_WORDS + "(" + COLUMN_WordsID +")"
                    +")");
            System.out.println("CREATE TABLE " + TABLE_QUIZWORDS);
            
            
            String[] language = {"English", "Vietnamese", "Japanese", "French"};
            for(int i = 0; i < language.length; i++){
            	db.execSQL("INSERT INTO "+ TABLE_LANGUAGE + " (" + COLUMN_LanguageName + ") VALUES ('" + language[i] + "')");
            }
            String[] category = {"Adjective", "Adverb", "Noun", "Verb"};
            for(int i = 0; i < category.length; i++){
            	db.execSQL("INSERT INTO "+ TABLE_CATEGORY + " (" + COLUMN_CategoryName + ") VALUES ('" + category[i] + "')");
            }
            
            
            Log.e("cannc-DataHelper", "onCreated OpenHelper - Database is created.!");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // TODO Auto-generated method stub
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_WORDS);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORY);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_LANGUAGE);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_HistoryTest);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
            onCreate(db);

        }
        
   }

}
